<?php 
/**
 *
 * @category    API
 * @package     Api_Object
 * @subpackage  Model
 * @author      trungpm
 */
class Api_Model_Object {
	const table_name = 'tblvgt_object';
	const table_contract = 'tblvgt_contract';
	
    /* Check that the name exists and is 300 characters or less long */
    function VerifyObjectName($ObjectName, $i, $Errors) {
        if ((strlen($ObjectName) < 1) or (strlen($ObjectName) > 300)) {
            $Errors[$i] = IncorrectObjectNameLength;
        }
        return $Errors;
    }
    
	/* Verify that the object nam is valid, and doesn't already exist.
     * author: trungpm
     */
    function VerifyObjectNameExists($ObjectName, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(object_id) as c FROM tblvgt_object WHERE object_name=N\''.$ObjectName.'\'';
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            $Errors[$i] = ObjectNameAlreadyExists;
        }
        DB_free_result($SearchResult);
        return $Errors;
    }
	
	/* Verify that the object nam is valid, and doesn't already exist.
     * author: trungpm
     */
    function VerifyObjectNumberExists($ObjectNumber, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(object_id) as c FROM tblvgt_object WHERE locked = 0 AND object_number=N\''.$ObjectNumber.'\'';
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            $Errors[$i] = ObjectNameAlreadyExists;
        }
        DB_free_result($SearchResult);
        return $Errors;
    }
    
	/* Get user by Object Name.
     * author: trungpm
     */
    function GetObjectByName($ObjectName, $user, $password) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		$ObjectName = DB_escape_string($ObjectName);
        $Errors = $this->VerifyObjectNameExists($ObjectName, sizeof($Errors), $Errors, $db);
        if (sizeof($Errors) == 0) {
            return $Errors;
        }
        $sql = 'SELECT object_id,
					object_number,
					object_name,
					object_company,
					object_address,
					object_type
				FROM tblvgt_object WHERE object_name=N\''. $ObjectName . '\'';
        $result = DB_Query($sql, $db);
        if (sizeof($Errors) != 0) {
            $arrResult = DB_fetch_array($result);
            DB_free_result($result);
            return $arrResult;
        } else {
            return $Errors;
        }
    }
	
	/* Get user by Object Name.
     * author: trungpm
     */
    function GetObjectByNumber($ObjectNumber, $user, $password) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		$ObjectNumber = DB_escape_string($ObjectNumber);
        $Errors = $this->VerifyObjectNumberExists($ObjectNumber, sizeof($Errors), $Errors, $db);
        if (sizeof($Errors) == 0) {
            return $Errors;
        }
        $sql = 'SELECT object.object_id,
					object.object_number,
					object.object_name,
					object.object_company,
					object.object_address,
					object.object_type,
					contract.contract_id,
					contract.contract_number
				FROM tblvgt_object as object
				LEFT JOIN tblvgt_contract as contract
				ON object.object_id = contract.object_id
				WHERE locked = 0 AND object_number=N\''. $ObjectNumber . '\'';
        $result = DB_Query($sql, $db);
        if (sizeof($Errors) != 0) {
            $arrResult = DB_fetch_array($result);
            DB_free_result($result);
            return $arrResult;
        } else {
            return $Errors;
        }
    }
    
	/* Get user by Object Name.
     * author: trungpm
     */
    function GetObjectById($ObjectId, $user, $password) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }		
        $sql = 'SELECT object.object_id,
					object.object_number,
					object.object_name,
					object.object_company,
					object.object_address,
					object.object_type,
					contract.contract_number
				FROM tblvgt_object as object
				LEFT JOIN tblvgt_contract as contract
				ON object.object_id = contract.object_id
				WHERE object.locked = 0 AND object.object_id=N\''. $ObjectId . '\'';
        $result = DB_Query($sql, $db);
        $arrResult = DB_fetch_array($result);
        DB_free_result($result);
        return $arrResult;      
    }
	
    /* Insert a new object */
    function InsertObject($ObjectDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        foreach ($ObjectDetails as $key=>$value) {
            $ObjectDetails[$key] = DB_escape_string($value);
        }
        $Errors = $this->VerifyObjectName($ObjectDetails['object_name'], sizeof($Errors), $Errors);
        $FieldNames = '';
        $FieldValues = '';
        foreach ($ObjectDetails as $key=>$value) {
            $FieldNames .= $key.', ';
            $FieldValues .= 'N\''.$value.'\', ';
        }		
        $sql = 'INSERT INTO tblvgt_object ('.substr($FieldNames, 0, -2).') '.'VALUES ('.substr($FieldValues, 0, -2).') ';
        if (sizeof($Errors) == 0) {
            $result = DB_Query($sql, $db);
            if (!$result) {
                $Errors[0] = DatabaseInsertFailed;
            }
        }
        return $Errors;
    }	
	
	/* 
	* Insert a new object and return key
	* huudatxm
	*/
    function insert($ObjectDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        
        $Errors = $this->VerifyObjectName($ObjectDetails['object_name'], sizeof($Errors), $Errors);

        $FieldNames = '';
        $FieldValues = '';
        foreach ($ObjectDetails as $key=>$value) {
            $FieldNames .= $key.", ";
            $FieldValues .= 'N\''.$value.'\', ';
        }
		$FieldNames .= 'created_date, ';
        $FieldValues .= 'N\''.date('Y-m-d').'\', ';
		$FieldNames .= 'created_by, ';
        $FieldValues .= 'N\''.$_SESSION['UserID'].'\', ';
		$FieldNames .= 'updated_date, ';
        $FieldValues .= 'N\''.date('Y-m-d').'\', ';
		$FieldNames .= 'updated_by, ';
        $FieldValues .= 'N\''.$_SESSION['UserID'].'\', ';
		
        $sql = 'INSERT INTO tblvgt_object ('.substr($FieldNames, 0, -2).') '.' VALUES ('.substr($FieldValues, 0, -2).') ';
        if (sizeof($Errors) == 0) {
            DB_Query($sql, $db);
            return $_SESSION['LastInsertId'];	
        }
        return 0;
    }
	
	/* 
	* update 
	* huudatxm
	*/
    /*
    function update($ObjectDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        
		$val_str = $ObjectDetails['field']. ' = \''.$ObjectDetails['value'].'\'';
		$val_str .= ', updated_date = \''.date('Y-m-d').'\' ';        
		$val_str .= ', updated_by = \''.$_SESSION['UserID'].'\' ';
        		
        $sql = 'UPDATE tblvgt_object SET '.$val_str;
		$sql .= ' WHERE object_id = '.$ObjectDetails['object_id'];
		
        if (sizeof($Errors) == 0) {			
            return DB_Query($sql, $db);
        }
        return 0;
    }*/
    
	function ModifyObject($ObjectId, $ObjectDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        foreach ($ObjectDetails as $key=>$value) {
            $ObjectDetails[$key] = DB_escape_string($value);
        }
        $sql = 'UPDATE tblvgt_object SET ';
		foreach ($ObjectDetails as $key => $value) {
			$sql .= $key.'=N\''.str_replace("\'", "''", $value).'\', ';
		}
        $sql = substr($sql, 0, -2).' WHERE object_id='.$ObjectId;
        
        if (sizeof($Errors) == 0) {
            $result = DB_Query($sql, $db);
            if (!isset($result) || !$result) {
                $Errors[0] = DatabaseUpdateFailed;
            } else {
                $Errors = array();
            }
        }
        return $Errors;
    }
    
	/* 
	 * update	 
     */
    function update($object_id, $field, $value) {
    	//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };
        $val_str = "$field = N'$value'";
		$val_str .= ', updated_date = N\''.date('Y-m-d').'\' ';        
		$val_str .= ', updated_by = N\''.$_SESSION['UserID'].'\' ';
        		
        $sql = "UPDATE ".self::table_name." SET $val_str";
		$sql .= " WHERE object_id = $object_id";
		
        if (sizeof($Errors) == 0) {			
            return DB_Query($sql, $db);          
        }
        return 0;
    }
	
	/* Get objects by conditions
     * author: datnh
     */
	
    function loadObjects($typeArr, $limit=null, $start=null, &$total, $user, $password, $condition = array()) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		
		$where = array();
		$conds = '';
		if(!empty($condition)){
			$arr = array();
			foreach ($condition as $key=>$value) {
				$arr[] = " $key LIKE $value ";
			}
			$conds = implode(" AND ", $arr);
			$where[] = $conds;
		}	
				
		if(isset($typeArr) && is_array($typeArr) && !empty($typeArr)){
			$where[] = "object_type IN (".implode(",", $typeArr).")";
		}
		
		if(!empty($where)){
			$where = "WHERE locked = 0 AND ".implode(" AND ", $where);        	
		}else{
			$where = "";
		}
		$sql = "SELECT COUNT(object_id) AS size FROM tblvgt_object $where";
        $stmt = DB_Query($sql, $db);
        $obj = DB_fetch_object($stmt);
        $total = $obj->size;
        
		DB_free_result($result);
		
		$sql = "SELECT row_number() OVER (ORDER BY o.object_id) AS rownum, 
					o.object_id, 
					o.object_number ,
					o.object_name,
					o.object_real_name,
					o.object_company,
					o.object_address,
					o.object_phone,
					o.object_mobile,
					o.object_email,
					o.object_fax,
					o.object_type, 
					c.contract_id,
					c.contract_number,
					convert(varchar, c.contract_date, 101) as contract_date,
					convert(varchar, c.contract_end, 101) as contract_end
				FROM   tblvgt_object AS o
				LEFT JOIN tblvgt_contract AS c ON o.object_id = c.object_id
				$where";	
	
		$sql2 .= "SELECT * FROM(".$sql.") AS A ";
		if(isset($start) && isset($limit)){
			$sql2 .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit))";
		}
		
        $result = DB_Query($sql2, $db);
		
        if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
    }
	
	/* 
	* delete 
	* huudatxm
	*/
    function delete($object_id, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        $checkId = $this->verifyFieldExist('object_id',$object_id);
		if($checkId == 1) {
			$val_str = 'locked = 1';
			$val_str .= ', updated_date = N\''.date('Y-m-d').'\' ';        
			$val_str .= ', updated_by = N\''.$_SESSION['UserID'].'\' ';	        		
	        $sql = "UPDATE ".self::table_name." SET $val_str WHERE object_id = $object_id";
			
	        if (sizeof($Errors) == 0) {			
	            return DB_Query($sql, $db);
	        }
		}
        return null;
    }
	
	/* 
     * author: datnh
     */
    function GetAllObject($user, $password) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		$SongName = DB_escape_string($SongName);
        $sql = 'SELECT object_id, object_number , object_name, object_type	
				FROM tblvgt_object WHERE locked = 0';				
		$result = DB_Query($sql, $db);
		
        if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }		
		DB_free_result($result);	
    }	
	
	function updateContractByObject($ObjectDetails, $user, $password) {
		$Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        
		$val_str = $ObjectDetails['field']. ' = N\''.$ObjectDetails['value'].'\'';
		$val_str .= ', updated_date = N\''.date('Y-m-d').'\' ';        
		$val_str .= ', updated_by = N\''.$_SESSION['UserID'].'\' ';
        		
        $sql = 'UPDATE tblvgt_contract SET '.$val_str;
		$sql .= ' WHERE object_id = '.$ObjectDetails['object_id'];
		
        if (sizeof($Errors) == 0) {			
            return DB_Query($sql, $db);
        }
        return 0;
	}
	
	/* 
     * author: trungpm
     */
	function GetMaxObjectNumber($type, $user, $password){
		$Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		$NewObjectNumber = null;
		$sql = 'SELECT max(object_number) AS object_number FROM tblvgt_object WHERE object_type=N\''. $type . '\'';
        $result = DB_Query($sql, $db);
		if (DB_has_rows($result)>0) {
			$ObjNumber = DB_fetch_object($result);
			$Number = $ObjNumber->object_number;
			$Number = intval(substr($Number, 2, strlen($Number)))+1;
			
			if((strlen($Number) < $_SESSION['MaxObjectNumber']) && (strlen($Number) > 0)){
				$lengthZero = $_SESSION['MaxObjectNumber'] - strlen($Number);
				$Number = str_repeat('0', $lengthZero).$Number;
			}
			// xet truong hop ca si hay nhac si
			switch($type){
				case 1: $NewObjectNumber = "CS".$Number; break;
				case 2: $NewObjectNumber = "NS".$Number; break;
				default: return;
			}			
        }
		
		return $NewObjectNumber;
	}
	
	//2010-11-14---------------------------------------------
	function getObjectPaginates($limit, $start, &$total, $objectConditions, $contractConditions, $user, $password) {
		//Check user permission
		$db = db($user,  $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };		
        //Get search conditions 
		$objectCondStr = null;
		if(!empty($objectConditions)){
			$arr = array();
			foreach ($objectConditions as $cond) {
				$arr[] = 't.'.$cond['field'].' '.$cond['relation'].' '.$cond['value'];
			}
			$objectCondStr .= implode(" AND ", $arr);
		};		
		$contractCondStr = null;
		if(!empty($contractConditions)){
			$arr = array();
			foreach ($contractConditions as $cond) {
				$arr[] = 't1.'.$cond['field'].' '.$cond['relation'].' '.$cond['value'];
			}
			$contractCondStr .= implode(" AND ", $arr);
		};
		$where = '';
		if(!empty($objectCondStr)) {	
			$where = 'WHERE '.$objectCondStr;
			if(!empty($contractCondStr)) {
				$where .= ' AND '.$contractCondStr;	
			}
		}elseif(!empty($contractCondStr)){
			$where = 'WHERE '.$contractCondStr;		
		};
		//Get query string for size
		$sql = "SELECT COUNT(*) AS size 
		FROM ".self::table_name." AS t
		LEFT JOIN ".self::table_contract." AS t1 ON t.object_id = t1.object_id
		$where";	
		
		//Get size 
        $result = DB_Query($sql, $db);
        $obj = DB_fetch_object($result);
        $total = $obj->size;		        
		DB_free_result($result);		
		//Get query string with row_num
		$query = "SELECT row_number() OVER (ORDER BY t.object_id) AS rownum, 
					t.object_id, 
					t.object_number ,
					t.object_name,
					t.object_real_name,
					t.object_company,
					t.object_address,
					t.object_phone,
					t.object_mobile,
					t.object_email,
					t.object_fax,
					t.object_type, 
					t1.contract_id,
					t1.contract_number,
					convert(varchar, t1.contract_date, 101) as contract_date,
					convert(varchar, t1.contract_end, 101) as contract_end
				FROM ".self::table_name." AS t
				LEFT JOIN ".self::table_contract." AS t1 ON t.object_id = t1.object_id
				$where";
				
		//Get query string with paginate
		$query = "SELECT * FROM(".$query.") AS A ";
		if(isset($start) && isset($limit)){
			$query .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit))";
		}	
		//Execute query
        $result = DB_Query($query, $db);
		//Return result
        if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
	}
	
	/* Verify field existed.	 
     */
    function verifyFieldExist($field, $value) {
    	//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };
        $Searchsql = "SELECT count(*) as c FROM ".self::table_name." WHERE $field = N'$value'";
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            return 1;
        };
        return 0;
    }
}

?>
